package utils

import (
	"database/sql"
	"fmt"
	"io/ioutil"
	"log"
	"nodemessage.com/ruov-cli/types"
	"strings"

	_ "github.com/go-sql-driver/mysql"
)

// DbGetDatabaseSchema 读取数据库中所有表的结构信息
func DbGetDatabaseSchema(dbUser, dbPassword, dbHost string, dbPort int, dbName string) ([]types.TableInfo, error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", dbUser, dbPassword, dbHost, dbPort, dbName)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("连接数据库失败: %v", err)
	}
	defer db.Close()

	// 获取所有表名和表注释
	query := `
		SELECT TABLE_NAME, TABLE_COMMENT
		FROM information_schema.TABLES
		WHERE TABLE_SCHEMA = ?
	`
	rows, err := db.Query(query, dbName)
	if err != nil {
		return nil, fmt.Errorf("查询表失败: %v", err)
	}
	defer rows.Close()

	var tables []types.TableInfo

	for rows.Next() {
		var tableName, tableComment string
		if err := rows.Scan(&tableName, &tableComment); err != nil {
			return nil, fmt.Errorf("读取表信息失败: %v", err)
		}

		// 获取表结构
		tableInfo, err := getTableStructure(db, dbName, tableName, tableComment)
		if err != nil {
			return nil, fmt.Errorf("获取表 %s 的结构失败: %v", tableName, err)
		}

		tables = append(tables, tableInfo)
	}

	return tables, nil
}

// getTableStructure 获取单个表的结构信息
func getTableStructure(db *sql.DB, dbName, tableName, tableComment string) (types.TableInfo, error) {
	query := `
		SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY, EXTRA, COLUMN_COMMENT
		FROM information_schema.COLUMNS
		WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
		ORDER BY ORDINAL_POSITION
	`
	rows, err := db.Query(query, dbName, tableName)
	if err != nil {
		return types.TableInfo{}, err
	}
	defer rows.Close()

	var columns []types.ColumnInfo

	for rows.Next() {
		var col types.ColumnInfo
		if err := rows.Scan(&col.Name, &col.DataType, &col.IsNullable, &col.ColumnKey, &col.Extra, &col.Comment); err != nil {
			return types.TableInfo{}, err
		}
		columns = append(columns, col)
	}

	return types.TableInfo{Name: tableName, Comment: tableComment, Columns: columns}, nil
}

// DbMySQLToJavaType 将 MySQL 数据类型转换为 Java 数据类型
func DbMySQLToJavaType(mysqlType string) string {
	// 转换为小写并去除可能的长度信息
	mysqlType = strings.ToLower(mysqlType)
	mysqlType = strings.Split(mysqlType, "(")[0]

	switch mysqlType {
	case "tinyint":
		return "Byte"
	case "smallint":
		return "Short"
	case "mediumint", "int", "integer":
		return "Integer"
	case "bigint":
		return "Long"
	case "float":
		return "Float"
	case "double", "real":
		return "Double"
	case "decimal", "numeric":
		return "BigDecimal"
	case "date", "datetime", "timestamp", "time":
		return "Date"
	case "year":
		return "Integer"
	case "char", "varchar", "tinytext", "text", "mediumtext", "longtext":
		return "String"
	case "binary", "varbinary", "tinyblob", "blob", "mediumblob", "longblob":
		return "byte[]"
	case "bit":
		return "Boolean"
	case "enum", "set":
		return "String"
	default:
		return "Object" // 默认返回 Object 类型
	}
}

// JavaTypeToImport 映射 Java 类型到 Java 导入包
var JavaTypeToImport = map[string]string{
	"String":     "import java.lang.String;",
	"Integer":    "import java.lang.Integer;",
	"Double":     "import java.lang.Double;",
	"Object":     "import java.lang.Object;",
	"Boolean":    "import java.lang.Boolean;",
	"List":       "import java.util.List;",
	"ArrayList":  "import java.util.ArrayList;",
	"HashMap":    "import java.util.HashMap;",
	"Map":        "import java.util.Map;",
	"Set":        "import java.util.Set;",
	"Date":       "import java.util.Date;",
	"BigDecimal": "import java.math.BigDecimal;",
}

// GetJavaImport 根据 Java 类型返回 Java 导入包
func GetJavaImport(javaType ...string) (javaImport []string) {
	for _, s := range javaType {
		if goImport, exists := JavaTypeToImport[s]; exists {
			javaImport = append(javaImport, goImport)
		}
	}
	return
}

// DbPing 测试MySQL连接
func DbPing(user, password, host string, port int, dbName string) (string, error) {
	// 构建DSN (Data Source Name)
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", user, password, host, port, dbName)

	// 尝试打开数据库连接
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return dsn, fmt.Errorf("error opening database: %v", err)
	}
	defer db.Close()

	// 测试连接
	err = db.Ping()
	if err != nil {
		return dsn, fmt.Errorf("error connecting to the database: %v", err)
	}

	return dsn, nil
}

// DbExportSQL 导出数据库表结构和数据到 SQL 文件
func DbExportSQL(user, password, host string, port int, dbName string, tables []string, outputFile string) error {
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", user, password, host, port, dbName)
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return fmt.Errorf("failed to connect database: %w", err)
	}
	defer db.Close()

	var sqlStatements []string

	// 如果 tables 为空，则获取所有表名
	if len(tables) == 0 {
		var allTables []string
		rows, err := db.Query("SHOW TABLES")
		if err != nil {
			return fmt.Errorf("failed to query tables: %w", err)
		}
		defer rows.Close()

		for rows.Next() {
			var table string
			if err := rows.Scan(&table); err != nil {
				return fmt.Errorf("failed to scan table name: %w", err)
			}
			allTables = append(allTables, table)
		}
		tables = allTables
	}

	for _, table := range tables {
		// 获取表结构
		var createStmt string
		err := db.QueryRow("SHOW CREATE TABLE " + table).Scan(&createStmt)
		if err != nil {
			return fmt.Errorf("failed to get create statement for table %s: %w", table, err)
		}
		sqlStatements = append(sqlStatements, createStmt)

		// 获取数据
		rows, err := db.Query("SELECT * FROM " + table)
		if err != nil {
			return fmt.Errorf("failed to query data from table %s: %w", table, err)
		}
		defer rows.Close()

		columns, err := rows.Columns()
		if err != nil {
			return fmt.Errorf("failed to get columns: %w", err)
		}

		for rows.Next() {
			values := make([]interface{}, len(columns))
			for i := range values {
				values[i] = new(sql.NullString)
			}

			if err := rows.Scan(values...); err != nil {
				return fmt.Errorf("failed to scan row: %w", err)
			}

			insertStmt := fmt.Sprintf("INSERT INTO %s (%s) VALUES (", table, strings.Join(columns, ", "))
			vals := make([]string, len(values))
			for i, v := range values {
				if ns, ok := v.(*sql.NullString); ok && ns.Valid {
					vals[i] = fmt.Sprintf("'%s'", ns.String)
				} else {
					vals[i] = "NULL"
				}
			}
			insertStmt += fmt.Sprintf("%s);\n", strings.Join(vals, ", "))
			sqlStatements = append(sqlStatements, insertStmt)
		}
	}

	// 写入 SQL 文件
	err = ioutil.WriteFile(outputFile, []byte(fmt.Sprintf("%s\n", sqlStatements)), 0644)
	if err != nil {
		return fmt.Errorf("failed to write SQL file: %w", err)
	}

	log.Printf("Database exported to %s", outputFile)
	return nil
}
